package com.fingard.dsp.localdb;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Map;

import com.fingard.FGBiz;
import com.fingard.db.JDBCHelper;
import com.fingard.text.StringHelper;
import com.fingard.io.FileHelper;

public class DbInitiator {

	public DbInitiator(){
	}

	public void initDb(){
		String dbFilePath = FGBiz.dspCenter.dspSet.localDbFile;

		if(StringHelper.isNullOrEmpty(dbFilePath)){
			dbFilePath = FGBiz.dspCenter.dspSet.getDefLocalDbFile();
			//如果配置为空，并且db文件也不存在，则直接返回
			if(!FileHelper.existFile(dbFilePath)){
				FGBiz.startLog.addLogNoTime("未启用本地数据库文件（不推荐）");
				return;
			}else{
				FGBiz.dspCenter.dspSet.setLocalDbFileIfEmpty(dbFilePath);
				FGBiz.startLog.addLog("设置本地数据库文件为"+dbFilePath);
			}
		}
		File dbFile = new File(dbFilePath);
		boolean isExist = false;
		if(!dbFile.exists()){
			try {
				if(!dbFile.getParentFile().exists()){
					dbFile.getParentFile().mkdirs();
				}
				dbFile.createNewFile();
				isExist = true;
			} catch (IOException e) {
				FGBiz.startLog.exception("DbInitiator", e, "创建本地数据库文件"+dbFilePath+"发生异常");
			}
		}else{
			isExist = true;
		}

		String dbTmpDir = FGBiz.dspCenter.dspSet.sqliteTmpdir;//StringHelper.getFileParentDir(FGBiz.dspCenter.dspSet.localDbFile);
		if(StringHelper.hasAnyChar(dbTmpDir)){
			FileHelper.mkdirsIfNotExists(dbTmpDir);
			dbTmpDir = StringHelper.trimEndFileSp(dbTmpDir)+File.separator;
			System.setProperty("org.sqlite.tmpdir", dbTmpDir);
		}
		FGBiz.startLog.addLogNoTime("org.sqlite.tmpdir:"+System.getProperty("org.sqlite.tmpdir"));
		FGBiz.startLog.addLogNoTime("java.io.tmpdir:"+System.getProperty("java.io.tmpdir"));
		FGBiz.startLog.addLogNoTime("本地数据库文件：" + FGBiz.dspCenter.dspSet.localDbFile);

		if(isExist){
			initTable();
		}
	}

	public void initTable(){
		Connection conn = null;
		try{
			conn = FGBiz.getLocalDbConn();
			/*
			try{
				String dbTmpDir = StringHelper.getFileParentDir(FGBiz.dspCenter.dspSet.localDbFile);
				dbTmpDir = StringHelper.trimEndFileSp(dbTmpDir)+File.separator+"dbtmp";
				FileHelper.mkdirsIfNotExists(dbTmpDir);
				String sqlPragma = "PRAGMA temp_store_directory = '"+dbTmpDir+"';";
				int rowUpdPragma = JDBCHelper.update(conn, sqlPragma, null);
				FGBiz.startLog.addLogNoTime("sqlite temp_store_directory:" + rowUpdPragma);
			}catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}
			*/
			String sqltab = null;
			Map<String,Object> tabMap= null;
			try{
				sqltab = "select name from sqlite_master where type='table' and name=?";
				tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"UNIQUEID"});
				if(tabMap==null || tabMap.size()==0){
					String sqlcreate = "CREATE TABLE UNIQUEID(KEYNAME VARCHAR(32) PRIMARY KEY NOT NULL,KEYVALUE BIGINT NOT NULL,ROWVERSION BIGINT NOT NULL);";
					FGBiz.logTrace.info("DbInitiator", sqlcreate);
					JDBCHelper.update(conn, sqlcreate, null);
				}
			}
			catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}

            try{
                sqltab = "select name from sqlite_master where type='table' and name=?";
                tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"YBTSEQNUM"});
                if(tabMap==null || tabMap.size()==0){
                    String sqlcreate = "CREATE TABLE YBTSEQNUM(URID BIGINT PRIMARY KEY NOT NULL,ASKNUMBER VARCHAR(24),SENDDATE VARCHAR(24) NOT NULL);";
                    FGBiz.logTrace.info("DbInitiator", sqlcreate);
                    JDBCHelper.update(conn, sqlcreate, null);
                }
            }
            catch(Exception ex){
                FGBiz.startLog.exception("DbInitiator", ex);
            }

            try{
                sqltab = "select name from sqlite_master where type='table' and name=?";
                tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"t_signnotify"});
                if(tabMap==null || tabMap.size()==0){
                    String sqlcreate = "create table t_signnotify\n" +
                            "(\n" +
                            "  urid                INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                            "  asknumber           VARCHAR2(32) not null,\n" +
							"  pushstatus          VARCHAR2(2) default 0 not null,\n" +
                            "  apporgid            VARCHAR2(32) not null,\n" +
                            "  isprivate           CHAR(1) not null,\n" +
                            "  isbatch             CHAR(1) default 1 not null,\n" +
                            "  bankid              VARCHAR2(32),\n" +
                            "  accountnumber       VARCHAR2(64) not null,\n" +
                            "  accountname         VARCHAR2(128) not null,\n" +
                            "  signno              VARCHAR2(64),\n" +
                            "  certtype            CHAR(1) not null,\n" +
                            "  cellphone           VARCHAR2(32) not null,\n" +
                            "  certnumber          VARCHAR2(64) not null,\n" +
                            "  accounttype         VARCHAR2(2) not null,\n" +
                            "  cvv2                VARCHAR2(20),\n" +
                            "  expireddate         CHAR(4),\n" +
                            "  lglrepnm            VARCHAR2(128),\n" +
                            "  lglrepidtp          VARCHAR2(2),\n" +
                            "  lglrepidno          VARCHAR2(64),\n" +
                            "  sourcenotecode      VARCHAR2(60),\n" +
                            "  policyno            VARCHAR2(60),\n" +
                            "  disabledate         DATE,\n" +
                            "  singlelimit         FLOAT,\n" +
                            "  limitperiodunit     VARCHAR2(2),\n" +
                            "  maxcntlimit         NUMBER(4),\n" +
                            "  notemoney           VARCHAR2(20),\n" +
                            "  insureid            VARCHAR2(60),\n" +
                            "  insurename          VARCHAR2(128),\n" +
                            "  createdon           DATE default (datetime('now', 'localtime')) not null,\n" +
                            "  lastmodifiedon      DATE default (datetime('now', 'localtime')) not null,\n" +
                            "  rowversion          NUMBER(4) default 1\n" +
                            ");";
                    FGBiz.logTrace.info("DbInitiator", sqlcreate);
                    JDBCHelper.update(conn, sqlcreate, null);
                }
            }
            catch(Exception ex){
                FGBiz.startLog.exception("DbInitiator", ex);
            }

			try{
				sqltab = "select name from sqlite_master where type='table' and name=?";
				tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"RECEIPTMAPPING"});
				if(tabMap==null || tabMap.size()==0){

					String sqlcreate = "CREATE TABLE RECEIPTMAPPING(URID BIGINT PRIMARY KEY NOT NULL,ASKNUMBER VARCHAR(24),BUSREF VARCHAR(64),RECEIPTNO VARCHAR(64),BUSDATE INT,OWNACT VARCHAR(64),REQBATCHNO VARCHAR(24),CUSTOMFILEID VARCHAR(64),BANK VARCHAR(8),MONEYWAY CHAR(1),AMOUNT DECIMAL(18,2),RECEIPTFILE VARCHAR(512),SRCFILE VARCHAR(512),CREATEDON DATE NOT NULL);\r\n"+
							"CREATE INDEX IXRCPTMAPASKNUM ON RECEIPTMAPPING (ASKNUMBER);\r\n"+
							"CREATE INDEX IXRCPTMAPBUSREF ON RECEIPTMAPPING (BUSREF);\r\n"+
							"CREATE INDEX IXRCPTMAPRECNO ON RECEIPTMAPPING (RECEIPTNO);\r\n"+
							"CREATE INDEX IXRCPTMAPRECNO ON RECEIPTMAPPING (BUSDATE,OWNACT);\r\n";
					FGBiz.logTrace.info("DbInitiator", sqlcreate);
					JDBCHelper.update(conn, sqlcreate, null);
					/*
					String sqlIndex = "";
					JDBCHelper.update(conn, sqlIndex, null);
					sqlIndex = "";
					JDBCHelper.update(conn, sqlIndex, null);
					sqlIndex = ";";
					JDBCHelper.update(conn, sqlIndex, null);
					sqlIndex = "";
					JDBCHelper.update(conn, sqlIndex, null);*/
				}
			}
			catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}

			try{
				sqltab = "select name from sqlite_master where type='table' and name=?";
				tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"REQSEQIDMAPPING"});
				if(tabMap==null || tabMap.size()==0){

					String sqlcreate = "CREATE TABLE REQSEQIDMAPPING(URID VARCHAR(32) PRIMARY KEY NOT NULL," +
							"BANKCODE VARCHAR(10)," +//渠道
							"TRANSDATE VARCHAR(10)," +//请求日期yyyy-mm-dd
							"TRANSTIME DATE," +//请求时间
							"REQBATCHNO VARCHAR(64)," +//请求批次号
							"REQSEQID VARCHAR(64)," +//请求流水号
							"BANKREQBATCHNO VARCHAR(64)," +//发送给银行的批次号
							"BANKREQSEQID VARCHAR(64)," +//发送给银行号的流水号
							"OUTBATCHNO VARCHAR(64)," +//银行返回的批次号
							"OUTREQSEQID VARCHAR(64)," +//银行返回的流水号
							"STATE VARCHAR(16)," +//状态
							"BUSITYPE VARCHAR(32)," +//业务类型
							"ISFINISHED CHAR(1)," +//交易是否结束
							"RESERVE1 VARCHAR(256)," +//保留字段1
							"RESERVE2 VARCHAR(256)," +//保留字段2
							"RESERVE3 VARCHAR(256));\r\n" +//保留字段3
							"CREATE UNIQUE INDEX BATCHNOREQSEQID ON REQSEQIDMAPPING (REQSEQID,REQBATCHNO);\r\n" +
							"CREATE INDEX REQBATCHNO ON REQSEQIDMAPPING (REQBATCHNO);\r\n";
					FGBiz.logTrace.info("DbInitiator", sqlcreate);
					JDBCHelper.update(conn, sqlcreate, null);
				}
			}
			catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}

			try{
				sqltab = "select name from sqlite_master where type='table' and name=?";
				tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"CANCELOPERATIONS"});
				if(tabMap==null || tabMap.size()==0){
					String sqlcreate = "CREATE TABLE CANCELOPERATIONS(\n" +
							"URID VARCHAR(32) PRIMARY KEY NOT NULL,\n" +
							"BANKCODE VARCHAR(10),\n" +//渠道
							"BILLNO VARCHAR(64),\n" +//票据号
							"BILLTYPE CHAR(1),\n" +//票据类型  1-银承 2-商承
							"TRANSFLAG CHAR(1),\n" +//票据类型  1-可转让 2-不可转让
							"ISSDATE VARCHAR(10),\n" +//票据出票日 yyyy-MM-dd
							"DUEDATE VARCHAR(10),\n" +//票据到期日
							"BILLAMOUNT  VARCHAR(64),\n" + //票据金额
							"DRAWERNAME  VARCHAR2(256),\n" +// 出票人名称
							"DRAWERACTNO  VARCHAR2(64),\n" +//出票人账号
							"DRAWERBANKCODE  VARCHAR2(32),\n" +//出票人银行编码
							"DRAWERBANKNAME  VARCHAR2(128),\n" +//出票人银行名称
							"DRAWERORGCODE  VARCHAR2(10),\n" +//出票人组织
							"ACCEPTORNAME  VARCHAR2(256),\n" +//承兑人名称
							"ACCEPTORACTNO  VARCHAR2(64),\n" +//承兑人账号
							"ACCEPTORBANKCODE    VARCHAR2(32),\n" +//承兑人银行号
							"ACCEPTORBANKNAME VARCHAR2(128),\n" +//承兑人开户银行名称
							"RECEIVERNAME   VARCHAR2(256),\n" +//收票人名称
							"RECEIVERACTNO    VARCHAR2(64),\n" +//收票人账号
							"RECEIVERBANKCODE VARCHAR2(32),\n" +//收票人银行号
							"RECEIVERBANKNAME   VARCHAR2(32),\n" +//收票人银行名称
							"APPLYORGCODE     VARCHAR2(10),\n" +//申请人组织
							"APPLYNAME   VARCHAR2(256),\n" +//申请人名称
							"APPLYACTNO    VARCHAR2(64),\n" +//申请人账号
							"APPLYBANKCODE VARCHAR2(32),\n" +//申请人银行号
							"APPLYBANKNAME VARCHAR2(128),\n" +//申请人开户行名称
							"RECNAME  VARCHAR2(256),\n" +//接收人名称
							"RECACTNO  VARCHAR2(64),\n" +//接收人账户
							"RECBANKCODE  VARCHAR2(32),\n" +//接收人银行号
							"RECBANKNAME  VARCHAR2(128),\n"+//接收人开户行名称
							"OUTBATCHNO VARCHAR(64),\n" +//批次号
							"OUTREQSEQID VARCHAR(64),\n" +//流水号
							"TRANSDATE VARCHAR(10),\n" +//交易日期
							"BUSITYPE VARCHAR(32),\n" +//业务种类
							"ISFINISHED CHAR(1),\n" +//是否结束 1-未结束 2-处理中 3-结束
							"ISCANCEL CHAR(1),\n" +//是否作废 1-未作废 2-已作废
							"REQUEST1 VARCHAR(256),\n" +
							"REQUEST2 VARCHAR(256),\n" +
							"REQUEST3 VARCHAR(256)); \n"+
							"CREATE UNIQUE INDEX REQBATCHNO ON CANCELOPERATIONS (OUTREQSEQID);\r\n";
					FGBiz.logTrace.info("DbInitiator", sqlcreate);
					JDBCHelper.update(conn, sqlcreate, null);
				}
			}
			catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}

//			try{
//				sqltab = "select name from sqlite_master where type='table' and name=?";
//				tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"SPDBNO"});
//				if(tabMap==null || tabMap.size()==0){
//
//					String sqlcreate = "CREATE TABLE SPDBNO(REQSEQID  PRIMARY KEY NOT NULL,BUSINESSID VARCHAR(24),BUSTYPE VARCHAR(8),TRANSDATE VARCHAR(8));\r\n";
//					JDBCHelper.update(conn, sqlcreate, null);
//				}
//			}
//			catch(Exception ex){
//				ex.printStackTrace();
//			}

			try{//回单客制化文件，已被分区表替代
				sqltab = "select name from sqlite_master where type='table' and name=?";
				tabMap=JDBCHelper.execQuerySingle(conn, sqltab,new Object[]{"RECEIPTCUSTOMFILE"});
				if(tabMap==null || tabMap.size()==0){

					String sqlcreate = "CREATE TABLE RECEIPTCUSTOMFILE(URID BIGINT PRIMARY KEY NOT NULL,FILEKEY VARCHAR(64),BUSDATE INT,OWNACT VARCHAR(64),CUSTOMFILEID VARCHAR(64),CREATEDON DATE NOT NULL);\r\n"+
							"CREATE UNIQUE INDEX IXUNRCPTCSTMFILE ON RECEIPTMAPPING (FILEKEY,BUSDATE,OWNACT);\r\n";
					FGBiz.logTrace.info("DbInitiator", sqlcreate);
					JDBCHelper.update(conn, sqlcreate, null);
					/*
					String sqlIndex = "";
					JDBCHelper.update(conn, sqlIndex, null);
					sqlIndex = "";
					JDBCHelper.update(conn, sqlIndex, null);
					sqlIndex = ";";
					JDBCHelper.update(conn, sqlIndex, null);
					sqlIndex = "";
					JDBCHelper.update(conn, sqlIndex, null);*/
				}
			}
			catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}
			
			try{//查回单的账号
				if(!PartitionTable.isExistTable(conn, "ACTQRYRECEIPT")){
					String sqlcreate = "CREATE TABLE ACTQRYRECEIPT(ACTNUM VARCHAR(128) NOT NULL," +
							"BANKCODE VARCHAR(16) NOT NULL," +//渠道
							"AREACODE VARCHAR(16)," +//区域代码
							"AREANAME VARCHAR(64)," +//区域名称
							"CMPTORG VARCHAR(64)," +//主管组织
							"CORPORATION VARCHAR(64)," +
							"ACTGROUP VARCHAR(64)," +
							"TENANT VARCHAR(64)," +
							"CREATEDON DATE NOT NULL);\r\n" +//创建时间
							"CREATE UNIQUE INDEX ACTNUMBNK ON ACTQRYRECEIPT (ACTNUM,BANKCODE);\r\n";
					FGBiz.logTrace.info("DbInitiator", sqlcreate);
					JDBCHelper.update(conn, sqlcreate, null);
				}
			}
			catch(Exception ex){
				FGBiz.startLog.exception("DbInitiator", ex);
			}
			
			FGBiz.startLog.addNoConsole("表:");
			ArrayList<String> tabList = LocalDbHelper.getTableNameList(conn);
			FGBiz.startLog.addNoConsole(StringHelper.join(", ", tabList));

		}catch(Exception ex){
			FGBiz.startLog.exception("DbInitiator", ex);
		}finally{
			if(conn != null){
				try{
					conn.close();
				}catch(Exception ex){
					FGBiz.startLog.exception("DbInitiator", ex);
				}
			}
		}
	}
}
